package com.xucia.jsponic.datasource;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import org.json.JSONArray;
import org.json.JSONObject;

import com.xucia.jsponic.data.ObjectId;
import com.xucia.jsponic.data.Persistable;
import com.xucia.jsponic.data.LazyPropertyId;

/**
 * 
 * @author Kris Zyp
 *Simple SQL database table data source
 */
public class DatabaseTableDataSource extends DatabaseDataSource implements WritableDataSource {
	String table;
	@Override
	ThreadSpecificConnectionObject setupConnection(Connection connection) throws SQLException {
		ConnectionStatements statements = new ConnectionStatements();
		statements.loadTable = connection.prepareStatement("select " + idColumn + " from " + table);
		statements.loadRow = connection.prepareStatement("select " + columnsString + " from " + table + " where " + idColumn + "=?");
		statements.deleteRow = connection.prepareStatement("DELETE FROM " + table + " WHERE " + idColumn + "=?");
		String valuesPlacement = "";
		for (int i = 0; i < columns.length;i++)
			valuesPlacement += i == 0 ? "?" : ",?";
		statements.insertRow = connection.prepareStatement("INSERT INTO " + table + " (" + columnsString + ") values (" + valuesPlacement + ")");
		return statements;
	}
	/**
	 * Just cast it to our specific connection object type
	 */
	@Override
	protected ConnectionStatements getConnectionObject() {
		return (ConnectionStatements) super.getConnectionObject();
	}
    class ConnectionStatements extends ThreadSpecificConnectionObject {
    	public PreparedStatement loadTable;
    	public PreparedStatement loadRow;
    	public PreparedStatement deleteRow;
    	public PreparedStatement insertRow;
    }
	@Override
	public void initParameters(JSONObject parameters) throws Exception {
    	table = parameters.getString("table");
    	idColumn = parameters.getString("idColumn");
    	JSONArray columnArray = parameters.getJSONArray("dataColumns");
    	columns = new String[columnArray.length()];
    	columnsString = "";
    	for (int i = 0; i < columns.length; i++) {
    		columns[i] = columnArray.getString(i);
    		columnsString += columns[i] + ",";
    	}
    	columnsString = columnsString.substring(0,columnsString.length()-1); // remove the last comma
    	super.initParameters(parameters);
	}
	String idColumn;
	String[] columns;
	String columnsString;
    public Object getFieldValue(LazyPropertyId valueId) throws Exception {
		throw new UnsupportedOperationException(); // we don't delay any values right now
	}
	public void mapObject(final PersistableInitializer initializer, final String objectId) throws Exception {
		tryExecution(new DatabaseAction() {
			public Object execute() throws SQLException {
				if ("".equals(objectId)) {
					// this is the root object access, so we return the list of rows in the table
					ResultSet rs = getConnectionObject().loadTable.executeQuery();
					List<ObjectId> resultList = new ArrayList<ObjectId>();
					while (rs.next()) {
						resultList.add(ObjectId.idForObject(DatabaseTableDataSource.this, rs.getString(1)));
					}
					rs.close();
					initializer.initializeList(resultList.iterator());
				}
				else {
					// we are going off the id of a specific row
					PreparedStatement loadStatement = getConnectionObject().loadRow;
					loadStatement.setString(1, objectId);
					ResultSet rs = loadStatement.executeQuery();
					rs.next();
					for (int i = 0; i < columns.length; i++) // load all the columns as properties
						initializer.setProperty(columns[i], rs.getObject(i+1));
					rs.close();
				}
				return null;
			}
		});
	}
	public void recordListAdd(String objectId, Object value) throws Exception {
		if (value instanceof ObjectId && !((ObjectId)value).isPersisted()) {
			final PreparedStatement insertRow = getConnectionObject().insertRow;
			((ObjectId)value).persistIfNeeded(new NewObjectPersister() {
				
				public Object getAcl() {
					return ObjectId.idForObject(DatabaseTableDataSource.this, objectId);
				}

				String objectId;
				public void start() throws Exception {
				}
				public void finished() throws Exception {
					objectId = Long.toString(executeAndGetGeneratedKey(insertRow));
				}

				public String getObjectId() {
					return objectId;
				}

				public WritableDataSource getSource() {
					return DatabaseTableDataSource.this;
				}

				public void initializeAsFunction(String functionBody) throws Exception {
					throw new UnsupportedOperationException();
				}

				public void initializeAsList(List values) throws Exception {
					throw new UnsupportedOperationException();
				}

				public void recordProperty(String name, Object value) throws Exception {
					for (int i = 0; i < columns.length; i++)
						if (columns[i].equalsIgnoreCase(name)) {
							insertRow.setObject(i+1, value);
							return;
						}
				}
			});
			return;
		}
		throw new RuntimeException("Can not insert " + value + " into table " + table);
	}
	public void recordListRemoval(String objectId, Object value) throws Exception {
		if (value instanceof ObjectId) {
			PreparedStatement deleteRow = getConnectionObject().deleteRow;
			if (((ObjectId)value).source != this)
				throw new RuntimeException("Can not delete an object that is not in this source");
			deleteRow.setLong(1,Long.parseLong(((ObjectId)value).subObjectId));
			deleteRow.execute();
			return;
		}
		throw new RuntimeException("Can not delete " + value + " from table " + table);
	}
	public void recordPropertyAddition(String objectId, String name, Object value) throws Exception {
		recordPropertyChange(objectId, name, value);
	}
	public void recordPropertyChange(String objectId, String name, Object value) throws Exception {
		for (int i = 0; i < columns.length; i++)
			if (columns[i].equals(name)) { // Do this to ensure it is valid name
				PreparedStatement statement = getConnectionObject().getConnection().prepareStatement("UPDATE " + table + " SET " + name + "=? WHERE " + idColumn + "=?");
				statement.setObject(1, value);
				statement.setString(2, objectId);
				statement.execute();
				return;
			}
		throw new RuntimeException("The column " + name + " is not registered as a valid column in this table");
	}
	public void recordPropertyRemoval(String objectId, String name) throws Exception {
		recordPropertyChange(objectId, name, null);
	}


}
